﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data;
using System.Reflection;
using Model;
using BLL;

namespace OA.Controllers
{
    public class InputToExcelController : Controller
    {
        //
        // GET: /InputToExcel/

        public ActionResult Index()
        {
            return View();
        }
        public ActionResult InputToExcel()
        {
            memberInfoManager MIManger=new memberInfoManager();
            List<MemberList> list = MIManger.GetAllMember().ToList();
            DataTable dt = LINQToDataTable(list);
            string fileName = "member" + DateTime.Now.ToShortDateString().Replace("_", "-") + ".xls";
            CreateExcel(dt, fileName);
            return View();
        }
        public void CreateExcel(DataTable dt, string fileName)
        {
            HttpResponse resp = System.Web.HttpContext.Current.Response;
            resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            resp.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
            string colHeaders = "", ls_item = "";
            ////定义表对象与行对象，同时用DataSet对其值进行初始化
            //DataTable dt = ds.Tables[0];
            DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
            int i = 0;
            int cl = dt.Columns.Count;
            
            //取得数据表各列标题，各标题之间以t分割，最后一个列标题后加回车符
            for (i = 0; i < cl; i++)
            {
                if (i == (cl - 1))//最后一列，加n
                {
                    colHeaders +=dt.Columns[i].Caption.ToString()+ "\n";
                }
                else
                {
                    colHeaders += dt.Columns[i].Caption.ToString() + "\t";
                }
            }
            resp.Write(colHeaders);
            //向HTTP输出流中写入取得的数据信息
            //逐行处理数据 
            foreach (DataRow row in myRow)
            {
                //当前行数据写入HTTP输出流，并且置空ls_item以便下行数据   
                for (i = 0; i < cl; i++)
                {
                    if (i == (cl - 1))//最后一列，加n
                    {
                        ls_item += row[i].ToString() + "\n";
                    }
                    else
                    {
                        ls_item += row[i].ToString()+ "\t";
                    }
                }
                resp.Write(ls_item);
                ls_item = "";
            }
            resp.End();

        }
        //将泛型转化成dataTable类型
        public DataTable LINQToDataTable<T>(IEnumerable<T> varlist)
        {   //定义要返回的DataTable对象
            DataTable dtReturn = new DataTable();
            // 保存列集合的属性信息数组
            PropertyInfo[] oProps = null;
            if (varlist == null) return dtReturn;//安全性检查
            //循环遍历集合，使用反射获取类型的属性信息
            foreach (T rec in varlist)
            {
                //使用反射获取T类型的属性信息，返回一个PropertyInfo类型的集合
                if (oProps == null)
                {
                    oProps = ((Type)rec.GetType()).GetProperties();
                    //循环PropertyInfo数组
                    foreach (PropertyInfo pi in oProps)
                    {
                        Type colType = pi.PropertyType;//得到属性的类型
                        //如果属性为泛型类型
                        if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition()
                        == typeof(Nullable<>)))
                        {   //获取泛型类型的参数
                            colType = colType.GetGenericArguments()[0];
                        }
                        //将类型的属性名称与属性类型作为DataTable的列数据
                        if (pi.Name == "memberId")
                        {
                            dtReturn.Columns.Add(new DataColumn("会员编号", colType));
                        }
                        if (pi.Name == "memberCardId")
                        {
                            dtReturn.Columns.Add(new DataColumn("会员卡号", colType));
                        }
                        if (pi.Name == "memberName")
                        {
                            dtReturn.Columns.Add(new DataColumn("会员姓名", colType));
                        }
                        if (pi.Name == "sex")
                        {
                            dtReturn.Columns.Add(new DataColumn("性别", colType));
                        }
                        if (pi.Name == "mobile")
                        {
                            dtReturn.Columns.Add(new DataColumn("手机", colType));
                        }
                        if (pi.Name == "Tel")
                        {
                            dtReturn.Columns.Add(new DataColumn("电话", colType));
                        }
                        if (pi.Name == "Email")
                        {
                            dtReturn.Columns.Add(new DataColumn("Email", colType));
                        }
                        if (pi.Name == "IDNumber")
                        {
                            dtReturn.Columns.Add(new DataColumn("身份证号", colType));
                        }
                        if (pi.Name == "birthDay")
                        {
                            dtReturn.Columns.Add(new DataColumn("出生日期", colType));
                        }
                        if (pi.Name == "cardType")
                        {
                            dtReturn.Columns.Add(new DataColumn("卡片类型", colType));
                        }
                        if (pi.Name == "Balance")
                        {
                            dtReturn.Columns.Add(new DataColumn("余额", colType));
                        }
                        if (pi.Name == "point")
                        {
                            dtReturn.Columns.Add(new DataColumn("积分", colType));
                        }
                        if (pi.Name == "status")
                        {
                            dtReturn.Columns.Add(new DataColumn("状态", colType));
                        }
                        if (pi.Name == "createDate")
                        {
                            dtReturn.Columns.Add(new DataColumn("办卡日期", colType));
                        }
                        if (pi.Name == "expiresDate")
                        {
                            dtReturn.Columns.Add(new DataColumn("过期日期", colType));
                        }
                        if (pi.Name == "consume")
                        {
                            dtReturn.Columns.Add(new DataColumn("消费", colType));
                        }
                        if (pi.Name == "memo")
                        {
                            dtReturn.Columns.Add(new DataColumn("附加信息", colType));
                        }
                        
                        if (pi.Name == "Marriage")
                        {
                            dtReturn.Columns.Add(new DataColumn("婚姻", colType));
                        }
                        if (pi.Name == "Zip")
                        {
                            dtReturn.Columns.Add(new DataColumn("邮编", colType));

                        }
                        if (pi.Name == "Address")
                        {
                            dtReturn.Columns.Add(new DataColumn("地址", colType));
                        }
                        if (pi.Name == "AreasID")
                        {
                            dtReturn.Columns.Add(new DataColumn("地区", colType));
                        }
                        if (pi.Name == "MemberType")
                        {
                            dtReturn.Columns.Add(new DataColumn("会员类型", colType));
                        }
                      
                        if (pi.Name == "tuiguangren")
                        {
                            dtReturn.Columns.Add(new DataColumn("推广人", colType));
                        }


                    }
                }
                //新建一个用于添加到DataTable中的DataRow对象
                DataRow dr = dtReturn.NewRow();
                //循环遍历属性集合
                foreach (PropertyInfo pi in oProps)
                {   //为DataRow中的指定列赋值

                    if (pi.Name == "memberId")
                    {
                        dr["会员编号"] = pi.GetValue(rec, null) == null ?
                              DBNull.Value : pi.GetValue(rec, null);
                    }
                    if (pi.Name == "memberCardId")
                    {
                        dr["会员卡号"] = pi.GetValue(rec, null) == null ?
                              DBNull.Value : pi.GetValue(rec, null);
                    }
                    if (pi.Name == "memberName")
                    {
                        dr["会员姓名"] = pi.GetValue(rec, null) == null ?
                              DBNull.Value : pi.GetValue(rec, null);
                    }
                    if (pi.Name == "sex")
                    {
                        dr["性别"] = pi.GetValue(rec, null) == null ?
                              DBNull.Value : pi.GetValue(rec, null);
                    }
                    if (pi.Name == "mobile")
                    {
                        dr["手机"] = pi.GetValue(rec, null) == null ?
                              DBNull.Value : pi.GetValue(rec, null);
                    }
                    if (pi.Name == "Tel")
                    {
                        dr["电话"] = pi.GetValue(rec, null) == null ?
                              DBNull.Value : pi.GetValue(rec, null);
                    }
                    if (pi.Name == "Email")
                    {
                        dr["Email"] = pi.GetValue(rec, null) == null ?
                              DBNull.Value : pi.GetValue(rec, null);
                    }
                    if (pi.Name == "IDNumber")
                    {
                        dr["身份证号"] = pi.GetValue(rec, null) == null ?
                              DBNull.Value : pi.GetValue(rec, null);
                    }
                    if (pi.Name == "birthDay")
                    {
                        dr["出生日期"] = pi.GetValue(rec, null) == null ?
                               DBNull.Value : pi.GetValue(rec, null);
                    }
                    if (pi.Name == "cardType")
                    {
                        dr["卡片类型"] = pi.GetValue(rec, null) == null ?
                               DBNull.Value : pi.GetValue(rec, null);
                    }
                    if (pi.Name == "Balance")
                    {
                        dr["余额"] = pi.GetValue(rec, null) == null ?
                               DBNull.Value : pi.GetValue(rec, null);
                    }
                    if (pi.Name == "point")
                    {
                        dr["积分"] = pi.GetValue(rec, null) == null ?
                              DBNull.Value : pi.GetValue(rec, null);
                    }
                    if (pi.Name == "status")
                    {
                        dr["状态"] = pi.GetValue(rec, null) == null ?
                              DBNull.Value : pi.GetValue(rec, null);
                    }
                    if (pi.Name == "createDate")
                    {
                        dr["办卡日期"] = pi.GetValue(rec, null) == null ?
                               DBNull.Value : pi.GetValue(rec, null);
                    }
                    if (pi.Name == "expiresDate")
                    {
                        dr["过期日期"] = pi.GetValue(rec, null) == null ?
                               DBNull.Value : pi.GetValue(rec, null);
                    }
                    if (pi.Name == "consume")
                    {
                        dr["消费"] = pi.GetValue(rec, null) == null ?
                               DBNull.Value : pi.GetValue(rec, null);
                    }
                    if (pi.Name == "memo")
                    {
                        dr["附加信息"] = pi.GetValue(rec, null) == null ?
                              DBNull.Value : pi.GetValue(rec, null);
                    }
                   
                    if (pi.Name == "Marriage")
                    {
                        dr["婚姻"] = pi.GetValue(rec, null) == null ?
                               DBNull.Value : pi.GetValue(rec, null);
                    }
                    if (pi.Name == "Zip")
                    {
                        dr["邮编"] = pi.GetValue(rec, null) == null ?
                               DBNull.Value : pi.GetValue(rec, null);

                    }
                    if (pi.Name == "Address")
                    {
                        dr["地址"] = pi.GetValue(rec, null) == null ?
                                DBNull.Value : pi.GetValue(rec, null);
                    }
                    if (pi.Name == "AreasID")
                    {
                        dr["地区"] = pi.GetValue(rec, null) == null ?
                               DBNull.Value : pi.GetValue(rec, null);
                    }
                    if (pi.Name == "MemberType")
                    {
                        dr["会员类型"] = pi.GetValue(rec, null) == null ?
                              DBNull.Value : pi.GetValue(rec, null);
                    }
                    
                    if (pi.Name == "tuiguangren")
                    {
                        dr["推广人"] = pi.GetValue(rec, null) == null ?
                              DBNull.Value : pi.GetValue(rec, null);
                    }

                }
                //将具有结果值的DataRow添加到DataTable集合中
                dtReturn.Rows.Add(dr);
            }
            return dtReturn;//返回DataTable对象
        }
    }
}
